﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace vncom
{
    public class DataProvider
    {
        public static string connectionString = ConfigurationManager.ConnectionStrings["VncomConnection"].ConnectionString;
        public static DataTable Query(string query)
        {
            DataTable data = new DataTable();
            SqlConnection cn = new SqlConnection(connectionString);
            cn.Open();
            SqlCommand cmd = new SqlCommand(query, cn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);

            da.Fill(data);
            return data;

        }

        public static DataSet NonSelectStoreProcedure(string ProcName)
        {
            DataSet data = new DataSet();
            SqlConnection cn = new SqlConnection(connectionString);
            cn.Open();
            SqlCommand cmd = new SqlCommand(ProcName, cn);

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(data);
            cn.Close();
            return data;

        }

        public static DataTable SelectStoreProcedure(string ProcName, List<SqlParameter> ParaArr)
        {
            DataTable data = new DataTable();
            SqlConnection cn = new SqlConnection(connectionString);
            cn.Open();
            SqlCommand cmd = new SqlCommand(ProcName, cn);
            foreach (SqlParameter para in ParaArr)
            {
                cmd.Parameters.Add(para);
            }
            cmd.CommandType = CommandType.StoredProcedure;

            SqlDataAdapter da = new SqlDataAdapter(cmd);

            da.Fill(data);
            cn.Close();
            return data;
        }

        public static DataTable SelectStoreProcedure(string ProcName)
        {
            DataTable data = new DataTable();
            SqlConnection cn = new SqlConnection(connectionString);
            cn.Open();
            SqlCommand cmd = new SqlCommand(ProcName, cn);

            cmd.CommandType = CommandType.StoredProcedure;

            SqlDataAdapter da = new SqlDataAdapter(cmd);

            da.Fill(data);
            cn.Close();
            return data;
        }

        public static SqlCommand OutPutProc(string procname, List<SqlParameter> ParaArr, SqlParameter output)
        {
            SqlConnection cn = new SqlConnection(connectionString);
            cn.Open();
            SqlCommand cmd = new SqlCommand(procname, cn);
            cmd.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter para in ParaArr)
            {
                cmd.Parameters.Add(para);
            }
            cmd.Parameters.Add(output).Direction = ParameterDirection.Output;
            cmd.ExecuteNonQuery();
            return cmd;

        }

        public static SqlCommand OutPutProc(string procname, SqlParameter output)
        {
            SqlConnection cn = new SqlConnection(connectionString);
            cn.Open();
            SqlCommand cmd = new SqlCommand(procname, cn);
            cmd.CommandType = CommandType.StoredProcedure;


            cmd.Parameters.Add(output).Direction = ParameterDirection.Output;
            cmd.ExecuteNonQuery();
            return cmd;

        }

        public static void NonqueryProc(string ProcName, List<SqlParameter> ParaArr)
        {
            SqlConnection cn = new SqlConnection(connectionString);
            cn.Open();
            SqlCommand cmd = new SqlCommand(ProcName, cn);
            foreach (SqlParameter para in ParaArr)
            {
                cmd.Parameters.Add(para);
            }
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.ExecuteNonQuery();
            cn.Close();
        }

        public static int Insert(string procname, List<SqlParameter> arrParam, string mID = "")
        {
            int _mID = 0;
            SqlConnection cn = new SqlConnection(connectionString);
            cn.Open();
            SqlCommand cmd = new SqlCommand(procname, cn);
            foreach (SqlParameter para in arrParam)
            {
                cmd.Parameters.Add(para);
            }
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(mID, SqlDbType.Int).Direction = ParameterDirection.Output;
            cmd.ExecuteNonQuery();
            _mID = Convert.ToInt32(cmd.Parameters[mID].Value);
            cn.Close();
            return _mID;
        }

        public static string getDomain(string _userName)
        {
            string Domain = "";
            DataTable data = new DataTable();
            SqlConnection cn = new SqlConnection(connectionString);
            cn.Open();
            SqlCommand cmd = new SqlCommand("User_GetByUserName", cn);
            SqlParameter UserName = new SqlParameter("@Username", SqlDbType.VarChar);
            UserName.Value = _userName;
            cmd.Parameters.Add(UserName);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(data);
            cn.Close();
            if (data != null)
            {
                if (data.Rows.Count > 0)
                {
                    Domain = data.Rows[0]["Domain"].ToString();
                }
            }
            return Domain;
        }

        public static DataTable getByUserName(string _userName)
        {
            DataTable data = new DataTable();
            SqlConnection cn = new SqlConnection(connectionString);
            cn.Open();
            SqlCommand cmd = new SqlCommand("User_GetByUserName", cn);
            SqlParameter UserName = new SqlParameter("@Username", SqlDbType.VarChar);
            UserName.Value = _userName;
            cmd.Parameters.Add(UserName);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(data);
            cn.Close();
            if (data != null)
            {
                if (data.Rows.Count > 0)
                {
                    return data;
                }
            }
            return null;
        }
    }
}